<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 7.1.&nbsp; Database Design</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7-SECT-2.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top"><a name="learnphpmysql-CHP-7-SECT-1"></a>
<h3 id="title-IDAGWHYH" class="docSection1Title">7.1. Database Design</h3>
<a name="IDX-CHP-7-0276"></a> 
<a name="IDX-CHP-7-0277"></a> 
<a name="IDX-CHP-7-0278"></a> 
<a name="IDX-CHP-7-0279"></a> 

<p class="docText">Designing your database properly is critical to your application performing well. Just like putting the printer all the way across your office, placing data in poor relationships makes work less efficient in that it can cause your database server to waste time looking for data. When thinking about you database, think about what kinds of questions will be asked when your database is used. For example, is this a valid username and password? Or, what are the details about a product for sale?</p>
<a name="learnphpmysql-CHP-7-SECT-1.1"></a>
<h4 id="title-IDABXHYH" class="docSection2Title">7.1.1. Relational Databases</h4>
<p class="docText">MySQL is a <span class="docEmphasis">relational</span> database. An important feature of relational systems is that a single database can be spread across several tables as opposed to our flat-file phone book example. Related data is stored in separate tables and allows you to put them together by using a key common to both tables. The <span class="docEmphasis">key</span> is the relation between the tables. The selection of a <span class="docEmphasis">primary key</span> is one of the most critical decisions you'll make in designing a new database. The most important concept that you need to understand is that you must ensure the selected key is unique. If it's possible that two records (past, present, or future) share the same value for an attribute, don't use them as a primary key. Including key fields from another table to form a link between tables is called a <span class="docEmphasis">foreign key</span> relationship, like a boss to employees or a user to a purchase. The relational model is very useful because data is retrieved easier and faster.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><td width="60" valign="top"><img src="images/tip_yellow.jpg" width="50" height="54" alt=""></td><TD valign="top">
<p class="docText">The name <span class="docEmphasis">relational databases</span> actually came from the original formal name for the tables, which was <span class="docEmphasis">relations</span>.</p>
</td></TR></table></TD></tr></table></P><br>
<p class="docText">Now that you have separate tables that store related data, you need to think about the number of items in each table that relate to the number of items in another table. This is all about relationships<a name="IDX-CHP-7-0280"></a> 
<a name="IDX-CHP-7-0281"></a> 
 and the type of relationships data falls into. Think of the relationship as a repository or bucket, and each bucket of data has a specific relationship.</p>

<a name="learnphpmysql-CHP-7-SECT-1.2"></a>
<h4 id="title-IDAPYHYH" class="docSection2Title">7.1.2. Relationship Types</H4>
<a name="IDX-CHP-7-0282"></a> 
<a name="IDX-CHP-7-0283"></a> 
<a name="IDX-CHP-7-0284"></a> 
<a name="IDX-CHP-7-0285"></a> 

<p class="docText">Databases relationships are quantified with the following categories:</p>
<ul><LI><p class="docList">One-to-one relationships</p></li><LI><p class="docList">One-to-many relationships</p></li><li><p class="docList">Many-to-many relationships</p></LI></UL>
<p class="docText">We'll discuss each of these relationships and provide an example. If you think of a family structure when thinking about relationships, you're ahead of the game. When you spend time alone with one parent, that's a specific type of relationship; when you spend time with both your parents, that's another one. If you bring in a significant partner and all of youyour parents, you, and your partnerall do something together, that's another relationship. This is identical to the bucket analogy. All those different types of relationships are like specific buckets that hold the dynamics of your relationships. In the database world, it's the data you've created.</p>
<a name="learnphpmysql-CHP-7-SECT-1.2.1"></a>
<h5 id="title-IDAZZHYH" class="docSection3Title">7.1.2.1. One-to-one relationships</h5>
<p class="docText">In a one-to-one<a name="IDX-CHP-7-0286"></a> 
<a name="IDX-CHP-7-0287"></a> 
 relationship, each item is related to one and only one other item. Within the example of a bookstore. A one-to-one relationship exists between users and their shipping addresses. Each user must have exactly one shipping address. The key symbol in each figure represents the field that's the key for the table, as shown in <a class="docLink" href="#learnphpmysql-CHP-7-FIG-2">Figure 7-2</a>.</p>
<a name="learnphpmysql-CHP-7-FIG-2"></a><P><center>
<h5 class="docFigureTitle">Figure 7-2. A one-to-one relationship between users and shipping addresses</H5>
<img border="0" alt="" width="478" height="155" SRC="images/learnphpmysql_0702.jpg">
</center></p><BR>
<p class="docText">In <a class="docLink" href="#learnphpmysql-CHP-7-FIG-3">Figure 7-3</a>, you see that the user <tt>mdavis</tt> has one and only one address, as do the users <tt>jphillips</tt> and <tt>suzieq</tt>.</P>
<a name="learnphpmysql-CHP-7-FIG-3"></a><P><center>
<h5 class="docFigureTitle">Figure 7-3. Some sample data for users and addresses</h5>
<img border="0" alt="" width="515" height="445" SRC="images/learnphpmysql_0703.jpg">
</center></p><br>

<a name="learnphpmysql-CHP-7-SECT-1.2.2"></a>
<h5 id="title-IDA01HYH" class="docSection3Title">7.1.2.2. One-to-many relationships</h5>
<a name="IDX-CHP-7-0288"></a> 
<a name="IDX-CHP-7-0289"></a> 

<p class="docText">A one-to-many<a name="IDX-CHP-7-0290"></a> 
<a name="IDX-CHP-7-0291"></a> 
 relationship, shown in <a class="docLink" href="#learnphpmysql-CHP-7-FIG-4">Figures 7-4</a> and <a class="docLink" href="#learnphpmysql-CHP-7-FIG-5">7-5</a>, has keys from one table that appear multiple times in another table. This is the most common type of relationship. For example, the categories for books such as hardcover, soft cover, and audio books. Each book is in one of those three categories. However, they're never in more than one category.</p>
<a name="learnphpmysql-CHP-7-FIG-4"></a><p><center>
<h5 class="docFigureTitle">Figure 7-4. A one-to-many relationship between format and books</h5>
<img border="0" alt="" width="478" height="154" SRC="images/learnphpmysql_0704.jpg">
</center></p><br>
<a name="learnphpmysql-CHP-7-FIG-5"></a><p><center>
<h5 class="docFigureTitle">Figure 7-5. Some sample books and their formats</H5>
<img border="0" alt="" width="549" height="372" SRC="images/learnphpmysql_0705.jpg">
</center></p><br>

<a name="learnphpmysql-CHP-7-SECT-1.2.3"></a>
<H5 id="title-IDA03HYH" class="docSection3Title">7.1.2.3. Many-to-many relationships</H5>
<p class="docText">A many-to-many<a name="IDX-CHP-7-0292"></a> 
<a name="IDX-CHP-7-0293"></a> 
 relationship means that two tables can each have multiple keys from the other table in them. For example, shoppers that use an online bookstore can purchase multiple books. Likewise, multiple users can purchase the same book title. <a class="docLink" href="#learnphpmysql-CHP-7-FIG-6">Figure 7-6</a> shows a many-to-many relationship between users and books purchased.</p>
<a name="learnphpmysql-CHP-7-FIG-6"></a><P><center>
<h5 class="docFigureTitle">Figure 7-6. A many-to-many relationship between users and books purchased</h5>
<img border="0" alt="" width="461" height="155" SRC="images/learnphpmysql_0706.jpg">
</center></p><BR>
<p class="docText">The many-to-many relationship is converted to a mapping table with two one-to-many relationships in order for the database to represent the data. <a class="docLink" href="#learnphpmysql-CHP-7-FIG-7">Figure 7-7</a> includes a mapping table for you to understand the connectivity between the relationships.</p>
<a name="learnphpmysql-CHP-7-FIG-7"></a><p><center>
<H5 class="docFigureTitle">Figure 7-7. Sample data for many-to-many scenario</h5>
<img border="0" alt="" width="549" height="251" SRC="images/learnphpmysql_0707.jpg">
</center></p><BR>
<p class="docText">Notice that both columns have repeating keys.</p>


<a name="learnphpmysql-CHP-7-SECT-1.3"></a>
<h4 id="title-IDAY5HYH" class="docSection2Title">7.1.3. Normalization</h4>
<a name="IDX-CHP-7-0294"></a> 
<a name="IDX-CHP-7-0295"></a> 
<a name="IDX-CHP-7-0296"></a> 

<p class="docText">Thinking about how your data is related and the most efficient way to organize it is called <span class="docEmphasis">normalization</span>.<a name="IDX-CHP-7-0297"></a> 
 Normalization of data is breaking it apart based on the logical relationships to minimize the duplication of data. Generally, duplicated data wastes space and makes maintenance a problem. Should you change information that is duplicated, there's the risk that you miss a portion and you risk inconsistencies in you database.</p>
<p class="docText">It's possible to have too much of a good thing though: databases placing each piece of data in their own tables would take too much processing time and queries would be convoluted. Finding a balance in between is the goal.</P>
<p class="docText">While the phone book example is very simple, the type of data that you process with a web page can benefit greatly from logically grouping related data.</P>
<p class="docText">Let's continue with the bookstore example. The site needs to keep track of the user's data, including login, address, and phone number, as well as information about the books, including the title, author, number of pages, and when each title was purchased.</p>
<p class="docText">Start by placing all of this information in one table, as shown in <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-1">Table 7-1</a>.</p>
<a name="learnphpmysql-CHP-7-TABLE-1"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 7-1. Essentially, a flat file, as there is only one table</H5></caption><colgroup span="10"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">User ID</P></th><th class="thead" scope="col" align="left"><p class="docText">First name</p></th><th class="thead" scope="col" align="left"><p class="docText">Last name</P></th><th class="thead" scope="col" align="left"><p class="docText">Address</P></th><th class="thead" scope="col" align="left"><p class="docText">Phone</P></th><th class="thead" scope="col" align="left"><p class="docText">Title</p></th><th class="thead" scope="col" align="left"><p class="docText">Author1</p></th><th class="thead" scope="col" align="left"><p class="docText">Author2</p></th><th class="thead" scope="col" align="left"><p class="docText">Pages</p></th><th class="thead" scope="col" align="left"><p class="docText">When</p></th></tr></thead><tr><td class="docTableCell" align="left"><p class="docText">Mdavis</p></td><td class="docTableCell" align="left"><p class="docText">Michele</p></td><td class="docTableCell" align="left"><p class="docText">Davis</P></td><td class="docTableCell" align="left"><p class="docText">7505 N. Linksway, Fx Pnt, MN, 55114</P></TD><td class="docTableCell" align="left"><p class="docText">414-352-4818</P></td><td class="docTableCell" align="left"><p class="docText">Linux in a Nutshell</p></TD><td class="docTableCell" align="left"><p class="docText">Ellen Siever</p></TD><td class="docTableCell" align="left"><p class="docText">Aaron Weber</p></TD><td class="docTableCell" align="left"><p class="docText">112</p></td><td class="docTableCell" align="left"><p class="docText">Sept 3rd, 2005</P></TD></tr><tr><td class="docTableCell" align="left"><p class="docText">Mdavis</p></TD><td class="docTableCell" align="left"><p class="docText">Michele</P></td><TD class="docTableCell" align="left"><p class="docText">Davis</P></TD><td class="docTableCell" align="left"><p class="docText">7505 N. Linksway, Fx Pnt, MN, 55114</p></td><td class="docTableCell" align="left"><p class="docText">414-352-4818</p></td><td class="docTableCell" align="left"><p class="docText">Classic Shell Scripting</p></td><td class="docTableCell" align="left"><p class="docText">Arnold Robbins</p></td><td class="docTableCell" align="left"><p class="docText">Nelson Beebe</p></TD><td class="docTableCell" align="left"><p class="docText">576</p></TD><TD class="docTableCell" align="left"><p class="docText">Sept 3rd, 2005</p></TD></tr></table></p><br>
<p class="docText">While combining the data into one table may seem like a good idea, it wastes space in the database and makes updating the data tedious. All the user data is repeated for each purchase. Additionally, if the user moves, then her address changes and each of her entries in the table has to be updated.</P>

<a name="learnphpmysql-CHP-7-SECT-1.4"></a>
<h4 id="title-IDAGHIYH" class="docSection2Title">7.1.4. Forms of Normalization</h4>
<p class="docText">To normalize a database, start with the most basic rules of normalization and move forward step by step. The steps of normalization are in three stages, called <span class="docEmphasis">forms</span>.<a name="IDX-CHP-7-0298"></a> 
 The first step, called First Normal Form (or FNF), must be done before the second normal form. Likewise, the third normal form cannot be completed before the second. The normalization process involves getting your data into conformity with the three progressive normal forms.</P>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><TD><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><td width="60" valign="top"><img src="images/tip_yellow.jpg" width="50" height="54" alt=""></td><td valign="top">
<p class="docText">A higher level of normalization cannot be achieved until the previous levels have done so already.</P>
</TD></tr></table></td></tr></table></p><BR>
<a name="learnphpmysql-CHP-7-SECT-1.4.1"></a>
<h5 id="title-IDA3HIYH" class="docSection3Title">7.1.4.1. First Normal Form</H5>
<a name="IDX-CHP-7-0299"></a> 
<a name="IDX-CHP-7-0300"></a> 
<a name="IDX-CHP-7-0301"></a> 
<a name="IDX-CHP-7-0302"></a> 

<p class="docText">The First Normal Form involves removal of redundant data from horizontal rows. You want to ensure that there is no duplication of data in a given row, and that every column stores the least amount of information possible.</p>
<p class="docText">Put simply, in order for your database to be in first normal form, it must satisfy two requirements. Every table must not have repeating columns that contain the same kind of data, and all columns must contain only one value.</P>
<p class="docText">The table in <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-1">Table 7-1</a> fails the repeating columns rule, because <tt>Author1</tt> and <tt>Author2</tt> store the same kind of information. This should be avoided, because you'll need to either add many author fields and waste space or you could potentially run out of fields to store the authors for a book that has many authors.</P>
<p class="docText">The solution is to break out the authors into a separate table that's linked to the books table.</P>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-7-TABLE-1">Table 7-1</a> also violates the rule for a column having only one value. The Address field contains more than one value as it stores the street address, city, state, and zip code. This makes searching on a single portion of the address, such as the city, difficult.</p>
<p class="docText">Furthermore, because users and book aren't really related, you would split them apart into <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-2">Tables 7-2</a>, <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-3">7-3</a>, <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-4">7-4</a>, and <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-5">7-5</a>.</p>
<a name="learnphpmysql-CHP-7-TABLE-2"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 7-2. The user purchases table after any normalization</h5></caption><colgroup span="11"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Purch-ase_ID (key)</p></th><th class="thead" scope="col" align="left"><p class="docText">User_ID</p></th><th class="thead" scope="col" align="left"><p class="docText">First name</p></th><th class="thead" scope="col" align="left"><p class="docText">Last name</p></th><th class="thead" scope="col" align="left"><p class="docText">Address</p></th><th class="thead" scope="col" align="left"><p class="docText">City</p></th><th class="thead" scope="col" align="left"><p class="docText">State</p></th><th class="thead" scope="col" align="left"><p class="docText">Zip</p></th><th class="thead" scope="col" align="left"><p class="docText">Phone</P></th><th class="thead" scope="col" align="left"><p class="docText">Purchased</p></th><th class="thead" scope="col" align="left"><p class="docText">When</p></th></TR></thead><TR><td class="docTableCell" align="left"><p class="docText">1</P></td><td class="docTableCell" align="left"><p class="docText">Mdavis</p></TD><td class="docTableCell" align="left"><p class="docText">Michele</p></TD><td class="docTableCell" align="left"><p class="docText">Davis</p></TD><td class="docTableCell" align="left"><p class="docText">7505 N. Linksway</p></td><td class="docTableCell" align="left"><p class="docText">FxPnt</P></TD><td class="docTableCell" align="left"><p class="docText">MN</p></td><td class="docTableCell" align="left"><p class="docText">55114</P></td><TD class="docTableCell" align="left"><p class="docText">414-352-4818</p></TD><TD class="docTableCell" align="left"><p class="docText">Linux in a Nutshell</P></td><td class="docTableCell" align="left"><p class="docText">Sept 3rd, 2005</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText">2</p></td><td class="docTableCell" align="left"><p class="docText">Mdavis</p></td><td class="docTableCell" align="left"><p class="docText">Michele</p></TD><td class="docTableCell" align="left"><p class="docText">Davis</p></TD><TD class="docTableCell" align="left"><p class="docText">7505 N. Linksway</p></TD><td class="docTableCell" align="left"><p class="docText">FxPnt</p></td><TD class="docTableCell" align="left"><p class="docText">MN</p></td><TD class="docTableCell" align="left"><p class="docText">55114</p></td><TD class="docTableCell" align="left"><p class="docText">414-352-4818</p></td><td class="docTableCell" align="left"><p class="docText">Classic Shell Scripting</p></TD><TD class="docTableCell" align="left"><p class="docText">Sept 3rd, 2005</p></td></tr></table></p><BR>
<a name="learnphpmysql-CHP-7-TABLE-3"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><H5 class="docTableTitle">Table 7-3. The books table after first normal form application</h5></caption><colgroup span="3"><col><col><col></colgroup><thead><TR><th class="thead" scope="col" align="left"><p class="docText">Title_ID (key)</P></th><th class="thead" scope="col" align="left"><p class="docText">Title</P></th><th class="thead" scope="col" align="left"><p class="docText">Pages</p></th></tr></thead><tr><td class="docTableCell" align="left"><p class="docText">1</p></td><td class="docTableCell" align="left"><p class="docText">Linux in a Nutshell</p></td><td class="docTableCell" align="left"><p class="docText">112</p></td></tr><tr><TD class="docTableCell" align="left"><p class="docText">2</p></td><TD class="docTableCell" align="left"><p class="docText">Classic Shell Scripting</P></td><TD class="docTableCell" align="left"><p class="docText">576</p></td></tr></table></P><br>
<a name="learnphpmysql-CHP-7-TABLE-4"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><H5 class="docTableTitle">Table 7-4. Authors now have their own table</h5></caption><colgroup span="2"><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Author_ID (key)</P></th><th class="thead" scope="col" align="left"><p class="docText">Author name</p></th></tr></thead><tr><td class="docTableCell" align="left"><p class="docText">1</P></TD><td class="docTableCell" align="left"><p class="docText">Ellen Siever</p></td></tr><TR><td class="docTableCell" align="left"><p class="docText">2</P></td><TD class="docTableCell" align="left"><p class="docText">Aaron Weber</P></TD></tr><tr><td class="docTableCell" align="left"><p class="docText">3</p></td><td class="docTableCell" align="left"><p class="docText">Arnold Robbins</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText">4</p></td><td class="docTableCell" align="left"><p class="docText">Nelson Beebe</P></td></tr></table></P><BR>
<a name="learnphpmysql-CHP-7-TABLE-5"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><H5 class="docTableTitle">Table 7-5. The book_author table links authors to books</h5></caption><colgroup span="2"><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Title_ID (key)</p></th><th class="thead" scope="col" align="left"><p class="docText">Author_ID (key)</P></th></tr></thead><tr><TD class="docTableCell" align="left"><p class="docText">1</p></td><TD class="docTableCell" align="left"><p class="docText">1</p></td></tr><tr><TD class="docTableCell" align="left"><p class="docText">1</P></td><td class="docTableCell" align="left"><p class="docText">2</p></td></TR><tr><TD class="docTableCell" align="left"><p class="docText">2</p></TD><TD class="docTableCell" align="left"><p class="docText">3</P></td></tr><tr><td class="docTableCell" align="left"><p class="docText">2</p></td><td class="docTableCell" align="left"><p class="docText">4</p></td></tr></table></p><br>
<p class="docText">We've effectively reduced each field to holding a single value, split apart related chunks of data into separate tables, and eliminated the repeating columns.</p>

<a name="learnphpmysql-CHP-7-SECT-1.4.2"></a>
<h5 id="title-IDAOYIYH" class="docSection3Title">7.1.4.2. Second Normal Form</H5>
<a name="IDX-CHP-7-0303"></a> 
<a name="IDX-CHP-7-0304"></a> 
<a name="IDX-CHP-7-0305"></a> 
<a name="IDX-CHP-7-0306"></a> 

<p class="docText">As we stated above, the First Normal Form deals with redundancy of data across a horizontal row. The Second Normal Form (or 2NF) deals with redundancy of data in vertical columns. Normal forms are progressive. To achieve Second Normal Form, your tables must already be in First Normal Form. For a database table to be in Second Normal Form, you must identify any columns that repeat their values across multiple rows. Those columns need to be placed in their own table and referenced by a key value in the original table.</p>
<p class="docText">You may notice that <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-2">Table 7-2</a> repeats the address information over multiple rows. In order to achieve Second Normal Form, you define a new addresses table to pull these out, creating <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-6">Tables 7-6</a> and <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-7">7-7</a>.</p>
<a name="learnphpmysql-CHP-7-TABLE-6"></a><P><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><H5 class="docTableTitle">Table 7-6. The Users table after second normal form application</h5></caption><colgroup span="8"><col><col><col><col><col><col><col><col></colgroup><thead><TR><th class="thead" scope="col" align="left"><p class="docText">User ID</p></th><th class="thead" scope="col" align="left"><p class="docText">First name</p></th><th class="thead" scope="col" align="left"><p class="docText">Last name</p></th><th class="thead" scope="col" align="left"><p class="docText">Address</P></th><th class="thead" scope="col" align="left"><p class="docText">City</p></th><th class="thead" scope="col" align="left"><p class="docText">State</p></th><th class="thead" scope="col" align="left"><p class="docText">Zip</P></th><th class="thead" scope="col" align="left"><p class="docText">Phone</p></th></tr></thead><TR><td class="docTableCell" align="left"><p class="docText">Mdavis</p></td><td class="docTableCell" align="left"><p class="docText">Michele</P></TD><td class="docTableCell" align="left"><p class="docText">Davis</p></td><td class="docTableCell" align="left"><p class="docText">7505 N. Linksway</P></td><TD class="docTableCell" align="left"><p class="docText">FxPnt</p></TD><TD class="docTableCell" align="left"><p class="docText">MN</P></td><td class="docTableCell" align="left"><p class="docText">55114</p></td><td class="docTableCell" align="left"><p class="docText">414-352-4818</p></td></tr></table></p><br>
<a name="learnphpmysql-CHP-7-TABLE-7"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 7-7. The Purchases table after second normal form application</h5></caption><colgroup span="3"><col><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">User ID</P></th><th class="thead" scope="col" align="left"><p class="docText">Purchased</p></th><th class="thead" scope="col" align="left"><p class="docText">When</p></th></TR></thead><TR><td class="docTableCell" align="left"><p class="docText">Mdavis</P></td><td class="docTableCell" align="left"><p class="docText">Linux in a Nutshell</p></TD><td class="docTableCell" align="left"><p class="docText">Sept 3rd, 2005</p></TD></tr><tr><TD class="docTableCell" align="left"><p class="docText">Mdavis</p></td><td class="docTableCell" align="left"><p class="docText">Classic Shell Scripting</p></TD><TD class="docTableCell" align="left"><p class="docText">Sept 3rd, 2005</p></td></tr></table></p><BR>
<p class="docText">Your data is now in great shape. You have separate tables for Users, Books, Authors, and Purchases.</p>

<a name="learnphpmysql-CHP-7-SECT-1.4.3"></a>
<H5 id="title-IDAGAJYH" class="docSection3Title">7.1.4.3. Third Normal Form</h5>
<a name="IDX-CHP-7-0307"></a> 
<a name="IDX-CHP-7-0308"></a> 
<a name="IDX-CHP-7-0309"></a> 
<a name="IDX-CHP-7-0310"></a> 

<p class="docText">If you've followed the First and Second Normal Form process, you may not need to do anything with your database to satisfy the Third Normal Form (or 3NF) rules. In Third Normal Form, you're looking for data in your tables that's not fully dependent on the primary key, but dependent on another value in the table. Where this applies to your tables isn't immediately clear.</P>
<p class="docText">In <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-6">Table 7-6</a>, the components of the addresses can be thought of as not being directly related to the user. The street address relies on the zip code, the zip code on the city, and finally, the city on the state. Third Normal Form requires that each of these be split out into separate tables, as shown in <a class="docLink" href="#learnphpmysql-CHP-7-FIG-8">Figure 7-8</a>.</P>
<a name="learnphpmysql-CHP-7-FIG-8"></a><P><center>
<h5 class="docFigureTitle">Figure 7-8. The address components broken out into separate tables</h5>
<img border="0" alt="" width="549" height="179" SRC="images/learnphpmysql_0708.jpg">
</center></p><br>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-7-FIG-8">Figure 7-8</a> shows how the address can be split up. The lines with the webbed feet represent the foreign key relationships. On a practical level, you may find that following the Third Normal Form creates more tables than you'll want to manage in your database. It's up to you to know where to stop normalizing your data.</p>
<p class="docText">It's a good idea to make sure your data at least conforms to Second Normal Form. The goal is to avoid data redundancy to prevent corruption and make the best possible use of storage. You also need to make sure that the same value is not stored in more than one place. With data in multiple locations, you have to perform multiple updates when the data needs to be changed, which can lead to corruption in your database.</p>
<p class="docText">As you noticed, the Third Normal Form removed even more data redundancy, but at the cost of simplicity and performance. In our example just shown, do you really expect the city and street names to change very regularly? In this situation, the Third Normal Form still prevents misspelling of city and street names. Since it's your database, you decide on the level of balance between normalization and the speed or simplicity of your database.</p>
<p class="docText">Now that we've covered the basics of how to lay out your data, we can delve into the details of how columns are defined.</p>


<a name="learnphpmysql-CHP-7-SECT-1.5"></a>
<h4 id="title-IDAJCJYH" class="docSection2Title">7.1.5. Column Data Types</h4>
<a name="IDX-CHP-7-0311"></a> 

<p class="docText">Although databases store the same information that you collect and process in PHP, databases require fields to be set to specific types of data when they're created.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><TR><td width="60" valign="top"><img src="images/warning_yellow.jpg" width="51" height="36" alt=""></td><TD valign="top">
<p class="docText">Remember, PHP isn't strongly typed, but databases are!</P>
</td></TR></table></td></tr></table></p><BR>
<p class="docText">A data type is classification of a particular type of information. When you read, you're used to conventions such as symbols, letters, and numbers. Therefore, it's easy to distinguish between different types of data because you use symbols along with numbers and letters. You can tell at a glance whether a number is a percentage, a time, or an amount of money. The symbols help you understand a percentage, time, or amount of money are that data's type. A database uses internal codes to keep track of the different types of data it processes.</p>
<p class="docText">Most programming languages require the programmer to declare the data type of every data object, and most database systems require the user to specify the type of each data field. The available data types<a name="IDX-CHP-7-0312"></a> 
<a name="IDX-CHP-7-0313"></a> 
 vary from one programming language to another, and from one database application to another. But, the three main types of datanumbers, dates/times, and stringsexist in one form or another. <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-8">Table 7-8</a> lists data types, with the values in brackets optional.</p>
<a name="learnphpmysql-CHP-7-TABLE-8"></a><P><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 7-8. Common MySQL data types</h5></caption><colgroup span="3"><col><col><col></colgroup><thead><TR><th class="thead" scope="col" align="left"><p class="docText">Field type</p></th><th class="thead" scope="col" align="left"><p class="docText">Description</p></th><th class="thead" scope="col" align="left"><p class="docText">Example</p></th></tr></thead><TR><TD class="docTableCell" align="left"><p class="docText">INT[(M)]</p></td><td class="docTableCell" align="left"><p class="docText">Integer number (max display size <tt>M</tt>)</p></TD><td class="docTableCell" align="left"><p class="docText">997</P></td></TR><TR><TD class="docTableCell" align="left"><p class="docText">FLOAT[(M,D)]</p></td><td class="docTableCell" align="left"><p class="docText">Decimal number (<tt>M</tt> places before the decimal <tt>D</tt> places after)</p></td><td class="docTableCell" align="left"><p class="docText">3.4156</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText">CHAR(M)</p></td><td class="docTableCell" align="left"><p class="docText">Characters (<tt>M</tt> characters up to 255)</P></td><td class="docTableCell" align="left"><p class="docText">"test"</P></TD></tr><TR><td class="docTableCell" align="left"><p class="docText">VARCHAR(M)</p></td><TD class="docTableCell" align="left"><p class="docText">Text (<tt>M</tt> characters up to 256)</p></td><TD class="docTableCell" align="left"><p class="docText">"testing 1, 2, 3"</p></td></TR><tr><td class="docTableCell" align="left"><p class="docText"><tt>TEXT</tt> or <tt>BLOB</tt></p></td><TD class="docTableCell" align="left"><p class="docText">Text up to 65,000 characters</P></td><td class="docTableCell" align="left"><p class="docText">"All work and no play makes Jack a dull boy. All</p><p class="docText">Work</p><p class="docText">&nbsp;&nbsp;&nbsp;And</P><p class="docText">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Play&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Makes Jack a dull boy."</p></TD></tr><TR><TD class="docTableCell" align="left"><p class="docText">DATE</P></td><td class="docTableCell" align="left"><p class="docText">Date YYYY-MM-DD</p></td><td class="docTableCell" align="left"><p class="docText">2003-12-25</p></td></tr><tr><td class="docTableCell" align="left"><p class="docText">TIME</p></td><td class="docTableCell" align="left"><p class="docText">Times HH:MM:SS</p></TD><td class="docTableCell" align="left"><p class="docText">11:36:02</p></TD></TR></table></p><BR>
<p class="docText">There are many more data types provided by MySQL; see <a class="docLink" target="_blank" href="http://dev.mysql.com/doc/mysql/en/column-types.html">http://dev.mysql.com/doc/mysql/en/column-types.html</a> for a complete list.</p>
<p class="docText">To define tables like <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-3">Tables 7-3</a> and <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-4">7-4</a>, use the types in <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-9">Tables 7-9</a> and <a class="docLink" href="#learnphpmysql-CHP-7-TABLE-10">7-10</a>.</p>
<a name="learnphpmysql-CHP-7-TABLE-9"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><H5 class="docTableTitle">Table 7-9. Books column data types</h5></caption><colgroup span="2"><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Field name</P></th><th class="thead" scope="col" align="left"><p class="docText">Database type</p></th></tr></thead><TR><td class="docTableCell" align="left"><p class="docText">Title_ID</p></td><td class="docTableCell" align="left"><p class="docText">INT</P></TD></tr><tr><td class="docTableCell" align="left"><p class="docText">Title</p></TD><td class="docTableCell" align="left"><p class="docText">VARCHAR(150)</P></td></TR><TR><TD class="docTableCell" align="left"><p class="docText">Pages</p></td><td class="docTableCell" align="left"><p class="docText">INT</p></td></tr></table></p><br>
<a name="learnphpmysql-CHP-7-TABLE-10"></a><p><table cellspacing="0" FRAME="hsides" RULES="all" cellpadding="4" width="100%"><caption><h5 class="docTableTitle">Table 7-10. Authors column data types</h5></caption><colgroup span="2"><col><col></colgroup><thead><tr><th class="thead" scope="col" align="left"><p class="docText">Field name</p></th><th class="thead" scope="col" align="left"><p class="docText">Database type</p></th></TR></thead><tr><td class="docTableCell" align="left"><p class="docText">Author_ID</P></TD><td class="docTableCell" align="left"><p class="docText">INT</P></td></tr><tr><TD class="docTableCell" align="left"><p class="docText">Title_ID</p></td><TD class="docTableCell" align="left"><p class="docText">INT</p></td></TR><tr><td class="docTableCell" align="left"><p class="docText">Author</p></td><TD class="docTableCell" align="left"><p class="docText">VARCHAR(100)</P></td></tr></table></p><br>
<p class="docText">The numeric ID fields, combined with a source of unique numbers, provide a way of guaranteeing the key field is unique. Specifying the <tt>auto_increment</tt> keyword when creating a column is a great way to generate a unique ID for a column. For example, if there are two authors with the name John Smith, and you use their names as a key, you'd have a problem keeping track of which Smith you're using. Keeping keys unique is an important part of making sure you have the correct data in your database. Next, we're going to move on to modifying objects, and learn about the language used to modify objects like tables and work with data.</P>


<a href="11011536.html"><img src="images/pixel.jpg" alt="" width="1" height="1" border="0"></a></TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7-SECT-2.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</html>
